package com.example.demo01.Impl.webImpl;

import com.example.demo01.Dao.webDao.XYValueDao;
import com.example.demo01.Entity.webEntity.XYValueEntity;
import com.example.demo01.utils.JDBCPGUtils;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class XYValueDaoImpl implements XYValueDao {

    /**
     * 获取全焦作范围内的报警次数统计信息
     * @return 全焦作范围内的报警次数集合
     */
    @Override
    public List<XYValueEntity> getWarnInfoListInJiaoZuo() {
        //结果集合
        List<XYValueEntity> list=new ArrayList<>();
        XYValueEntity xyValueEntity=null;
        //SQL语句
        String sql="select \n" +
                "\tcount(*) as value,\n" +
                "\tst_x(ST_Transform(st_geomfromtext('POINT('||wlon||' '||wlat||')',4326),3857)) as x,\n" +
                "\tst_y(ST_Transform(st_geomfromtext('POINT('||wlon||' '||wlat||')',4326),3857)) as y\n" +
                "from warnrecord,jiaozuopolygon\n" +
                "where  st_within(st_geomfromtext('POINT('||wlon||' '||wlat||')',4326),jiaozuopolygon.geom)='t'\n" +
                "group by warnrecord.wlon,warnrecord.wlat\n" +
                "order by value ASC";
        //声明变量
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        //数据库操作
        try {
            //获取结果集
            connection= JDBCPGUtils.getConnection();
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery();
            //解析结果集
            while (resultSet.next()){
                xyValueEntity=new XYValueEntity(
                        resultSet.getFloat(2),
                        resultSet.getFloat(3),
                        resultSet.getFloat(1)
                );
                list.add(xyValueEntity);
            }

//            System.out.println(list.toString());
//            System.out.println(minX+"\t"+minY+"\t"+maxX+"\t"+maxY);
            return proTransferToScreen(list);
        } catch (SQLException e) {
            e.printStackTrace();
            return list;
        } finally {
            JDBCPGUtils.close(resultSet,statement,connection);
        }
    }

    /**
     * 根据区县名称获取报警次数统计信息
     * @return 区县名称获取报警次数统计信息
     */
    @Override
    public List<XYValueEntity> getWarnInfoListByDomainName(String domain) {
        //结果集合
        List<XYValueEntity> list=new ArrayList<>();
        XYValueEntity xyValueEntity=null;
        //SQL语句
        String sql="select \n" +
                "\tcount(*) as value,\n" +
                "\tst_x(ST_Transform(st_geomfromtext('POINT('||wlon||' '||wlat||')',4326),3857)) as x,\n" +
                "\tst_y(ST_Transform(st_geomfromtext('POINT('||wlon||' '||wlat||')',4326),3857)) as y\n" +
                "from warnrecord,jiaozuo\n" +
                "where jiaozuo.name=? and st_within(st_geomfromtext('POINT('||wlon||' '||wlat||')',4326),jiaozuo.geom)='t'\n" +
                "group by warnrecord.wlon,warnrecord.wlat\n" +
                "order by value ASC";
        //声明变量
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        //数据库操作
        try{
            connection=JDBCPGUtils.getConnection();
            statement=connection.prepareStatement(sql);
            statement.setString(1,domain);
            resultSet=statement.executeQuery();
            //解析结果集合
            while (resultSet.next()){
                xyValueEntity=new XYValueEntity(
                        resultSet.getFloat(2),
                        resultSet.getFloat(3),
                        resultSet.getFloat(1)
                );
                list.add(xyValueEntity);
            }
            return proTransferToScreen(list);
        } catch (SQLException e) {
            e.printStackTrace();
            return list;
        }
    }

    /**
     * 获取全焦作范围内的报警次数统计信息——经纬度坐标
     * @return 全焦作范围内的报警次数集合
     */
    @Override
    public List<XYValueEntity> getWarnInfoListInJiaoZuoWGS84() {
        //结果集
        List<XYValueEntity> list=new ArrayList<>();
        XYValueEntity xyValueEntity=null;
        //SQL语句
        String sql="select\n" +
                "\tcount(*) as value,\n" +
                "\twlon as lon,\n" +
                "\twlat as lat\n" +
                "from warnrecord,jiaozuopolygon\n" +
                "where  st_within(st_geomfromtext('POINT('||wlon||' '||wlat||')',4326),jiaozuopolygon.geom)='t'\n" +
                "group by warnrecord.wlon,warnrecord.wlat\n" +
                "order by value ASC";
        //声明变量
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        //数据库操作
        try{
            connection=JDBCPGUtils.getConnection();
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery();
            //获取结果集
            while (resultSet.next()){
                xyValueEntity=new XYValueEntity(
                    resultSet.getFloat("lon"),
                        resultSet.getFloat("lat"),
                        resultSet.getFloat("value")
                );
                list.add(xyValueEntity);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return list;
        } finally {
            //资源释放
            JDBCPGUtils.close(resultSet,statement,connection);
        }
    }

    /**
     * 根据区县名称获取报警次数统计信息——投影坐标
     * @return 区县名称获取报警次数统计信息
     */
    @Override
    public List<XYValueEntity> getWarnInfoListByDomainNameWGS84(String domain) {
        //结果集
        List<XYValueEntity> list=new ArrayList<>();
        XYValueEntity xyValueEntity=null;
        String sql="select \n" +
                "wlon as lon,\n" +
                "wlat as lat,\n" +
                "count(*) as value\n" +
                "from warnrecord,jiaozuo\n" +
                "where jiaozuo.name=? and st_within(st_geomfromtext('POINT('||wlon||' '||wlat||')',4326),jiaozuo.geom)='t'\n" +
                "group by warnrecord.wlon,warnrecord.wlat\n" +
                "order by value ASC";
        //声明变量
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        //数据库操作
        try{
            connection=JDBCPGUtils.getConnection();
            statement=connection.prepareStatement(sql);
            statement.setString(1,domain);
            resultSet=statement.executeQuery();
            //获取结果集
            while (resultSet.next()){
                xyValueEntity=new XYValueEntity(
                        resultSet.getFloat("lon"),
                        resultSet.getFloat("lat"),
                        resultSet.getFloat("value")
                );
                list.add(xyValueEntity);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return list;
        } finally {
            //资源释放
            JDBCPGUtils.close(resultSet,statement,connection);
        }
    }

    /**
     * 获取客户信息——投影坐标
     */
    @Override
    public List<XYValueEntity> getUserInfoListInJiaoZuo() {
        List<XYValueEntity> list=new ArrayList<>();
        XYValueEntity xyValueEntity=null;
        String sql="select distinct\n" +
                "\tst_x(ST_Transform(st_geomfromtext('POINT('||lon||' '||lat||')',4326),3857)) as x,\n" +
                "\tst_y(ST_Transform(st_geomfromtext('POINT('||lon||' '||lat||')',4326),3857)) as y,\n" +
                "\tcount(*) as value\n" +
                "from userinfo1\n" +
                "group by x,y";
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
            connection=JDBCPGUtils.getConnection();
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery();
            while (resultSet.next()){
                xyValueEntity=new XYValueEntity(
                    resultSet.getFloat("x"),
                        resultSet.getFloat("y"),
                        resultSet.getFloat("value")
                );
                list.add(xyValueEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return proTransferToScreen(list);
        } finally {
            JDBCPGUtils.close(resultSet,statement,connection);
        }
        return proTransferToScreen(list);
    }

    /**
     * 获取客户信息——经纬度坐标
     */
    @Override
    public List<XYValueEntity> getUserInfoListInJiaoZuoWGS84() {
        //结果集
        List<XYValueEntity> list=new ArrayList<>();
        XYValueEntity xyValueEntity=null;
        String sql="select \n" +
                "\tlon as x,\n" +
                "\tlat as y,\n" +
                "\tcount(*) as value\n" +
                "from userinfo1\n" +
                "group by lon,lat";
        //声明变量
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
            connection=JDBCPGUtils.getConnection();
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery();
            //获取结果集
            while (resultSet.next()){
                xyValueEntity=new XYValueEntity(
                        resultSet.getFloat("x"),
                        resultSet.getFloat("y"),
                        resultSet.getFloat("value")
                );
                list.add(xyValueEntity);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return list;
        } finally {
            JDBCPGUtils.close(resultSet,statement,connection);
        }
    }

    /**
     * 将原始坐标重投影到800*800大小的屏幕空间坐标系中
     * @param list
     * @return 映射后的数据集
     */
    public List<XYValueEntity> proTransferToScreen(List<XYValueEntity> list){
        //获取最值
        Double minX= list.stream().mapToDouble(XYValueEntity->XYValueEntity.getX()).min().getAsDouble();
        Double minY=list.stream().mapToDouble(XYValueEntity->XYValueEntity.getY()).min().getAsDouble();
        Double maxX=list.stream().mapToDouble(XYValueEntity->XYValueEntity.getX()).max().getAsDouble();
        Double maxY=list.stream().mapToDouble(XYValueEntity->XYValueEntity.getY()).max().getAsDouble();
        //属性映射
        list.stream().forEach(e -> {
            e.setX((float) ((e.getX()-minX)/(maxX-minX)*800.0));
            e.setY((float) ((maxY-e.getY())/(maxY-minY)*800.0));
        });
        return list;
    }



}
